﻿using MvcMedicalProvider.Helpers;
using MvcMedicalProvider.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;

namespace MvcMedicalProvider.DataAccess
{
    public class ProviderDAO : BaseDAO
    {
        #region Singleton
        private static readonly ProviderDAO instance = new ProviderDAO();
        /// <summary>
        /// Singleton
        /// </summary>
        public static ProviderDAO Instance
        {
            get
            {
                return instance;
            }
        }

        private ProviderDAO()
        {
        }
        #endregion

        public List<ProviderDetails> GetProviderDetails(string providerType, string zipCode, List<string> listZipCodeInRange,
            string firstName, string lastName, string sortBy, string sortDirection)
        {
            RefineLastNameFirstName(ref firstName, ref lastName);
            sortBy = DBSafe.RemoveSpecialCharacter(sortBy);
            sortDirection = DBSafe.RemoveSpecialCharacter(sortDirection);

            var results = new List<ProviderDetails>();

            using (var dbConnection = CreateSqlConnection())
            {
                dbConnection.Open();

                var sqlString = GetSqlString(providerType, DBSafe.RemoveSpecialCharacter(zipCode), listZipCodeInRange,
                    DBSafe.RemoveSpecialCharacter(firstName), DBSafe.RemoveSpecialCharacter(lastName));

                //var directionSql = "";
                //if (sortDirection == "desc")
                //{
                //    directionSql = " desc";
                //}

                //var sortBySql = "";
                //if (sortBy == "1")
                //{
                //    sortBySql = " Order by [Filter1].nppes_provider_last_org_name" + directionSql + ", [Filter1].[nppes_provider_first_name] "
                //        + directionSql + ", [Filter1].nppes_credentials " + directionSql + "";
                //}
                //else if (sortBy == "2")
                //{
                //    sortBySql = " Order by [Filter1].[average_submitted_chrg_amt]" + directionSql;
                //}
                //else if (sortBy == "3")
                //{
                //    sortBySql = " Order by [Filter1].[average_Medicare_payment_amt]" + directionSql;
                //}

                //sqlString += sortBySql;

                var sqlCommand = CreateSqlCommand(sqlString, dbConnection);
                sqlCommand.CommandType = System.Data.CommandType.Text;

                using (IDataReader dr = sqlCommand.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        var providerId = DBSafe.GetIntFromDB(dr, "ProviderId");

                        var pInfo = new ProviderInfo();
                        pInfo.ProviderId = providerId;
                        pInfo.nppes_provider_zip = dr["nppes_provider_zip"].ToString();
                        pInfo.nppes_provider_gender = dr["nppes_provider_gender"].ToString();
                        pInfo.nppes_provider_street1 = dr["nppes_provider_street1"].ToString();
                        pInfo.nppes_provider_first_name = dr["nppes_provider_first_name"].ToString();
                        pInfo.nppes_provider_last_org_name = dr["nppes_provider_last_org_name"].ToString();
                        pInfo.nppes_credentials = dr["nppes_credentials"].ToString();
                        pInfo.nppes_provider_city = dr["nppes_provider_city"].ToString();
                        pInfo.nppes_provider_state = dr["nppes_provider_state"].ToString();

                        var providerDetails = new ProviderDetails();

                        providerDetails.ProviderInfo = pInfo;

                        providerDetails.ProviderDetailsId = DBSafe.GetIntFromDB(dr, "ProviderDetailsId");
                        providerDetails.ProviderId = providerId;
                        providerDetails.hcpcs_code = dr["hcpcs_code"].ToString();
                        providerDetails.line_srvc_cnt = DBSafe.GetIntFromDB(dr, "line_srvc_cnt");
                        providerDetails.bene_unique_cnt = DBSafe.GetIntFromDB(dr, "bene_unique_cnt");
                        providerDetails.bene_day_srvc_cnt = DBSafe.GetIntFromDB(dr, "bene_day_srvc_cnt");
                        providerDetails.average_Medicare_allowed_amt = DBSafe.GetDecimalFromDB(dr, "average_Medicare_allowed_amt");
                        providerDetails.stdev_Medicare_allowed_amt = DBSafe.GetDecimalFromDB(dr, "stdev_Medicare_allowed_amt");
                        providerDetails.average_submitted_chrg_amt = DBSafe.GetDecimalFromDB(dr, "average_submitted_chrg_amt");
                        providerDetails.stdev_submitted_chrg_amt = DBSafe.GetDecimalFromDB(dr, "stdev_submitted_chrg_amt");
                        providerDetails.average_Medicare_payment_amt = DBSafe.GetDecimalFromDB(dr, "average_Medicare_payment_amt");
                        providerDetails.stdev_Medicare_payment_amt = DBSafe.GetDecimalFromDB(dr, "stdev_Medicare_payment_amt");

                        results.Add(providerDetails);
                    }
                }
            }

            return results;
        }

        private static void RefineLastNameFirstName(ref string firstName, ref string lastName)
        {
            if (string.Compare(firstName, Constants.Constants.Optional, StringComparison.OrdinalIgnoreCase) == 0)
            {
                firstName = "";
            }

            if (string.Compare(lastName, Constants.Constants.Optional, StringComparison.OrdinalIgnoreCase) == 0)
            {
                lastName = "";
            }
        }

        private string GetSqlString(string providerType, string zipCode, List<string> listZipCodeInRange, string firstName, string lastName)
        {
            var sb = new StringBuilder();
            sb.AppendFormat("Select top 600 [Filter1].[ProviderDetailsId] AS [ProviderDetailsId],");
            sb.AppendFormat("[Filter1].[ProviderId1] AS [ProviderId],");
            sb.AppendFormat("[Filter1].[hcpcs_code] AS [hcpcs_code],");
            sb.AppendFormat("[Filter1].[line_srvc_cnt] AS [line_srvc_cnt], ");
            sb.AppendFormat("[Filter1].[bene_unique_cnt] AS [bene_unique_cnt], ");
            sb.AppendFormat("[Filter1].[bene_day_srvc_cnt] AS [bene_day_srvc_cnt], ");
            sb.AppendFormat("[Filter1].[average_Medicare_allowed_amt] AS [average_Medicare_allowed_amt], ");
            sb.AppendFormat("[Filter1].[stdev_Medicare_allowed_amt] AS [stdev_Medicare_allowed_amt], ");
            sb.AppendFormat("[Filter1].[average_submitted_chrg_amt] AS [average_submitted_chrg_amt], ");
            sb.AppendFormat("[Filter1].[stdev_submitted_chrg_amt] AS [stdev_submitted_chrg_amt], ");
            sb.AppendFormat("[Filter1].[average_Medicare_payment_amt] AS [average_Medicare_payment_amt], ");
            sb.AppendFormat("[Filter1].[stdev_Medicare_payment_amt] AS [stdev_Medicare_payment_amt],");
            sb.AppendFormat("[Filter1].[nppes_provider_first_name], [Filter1].nppes_provider_last_org_name,");
            sb.AppendFormat("[Filter1].nppes_provider_gender,[Filter1].[nppes_provider_zip],");
            sb.AppendFormat("[Filter1].nppes_credentials, [Filter1].nppes_provider_street1, [Filter1].nppes_provider_city, [Filter1].[nppes_provider_state] ");
            sb.AppendFormat(" FROM   (SELECT pd.[ProviderDetailsId] AS [ProviderDetailsId],");
            sb.AppendFormat("pd.[ProviderId] AS [ProviderId1], pd.[hcpcs_code] AS [hcpcs_code], ");
            sb.AppendFormat("pd.[line_srvc_cnt] AS [line_srvc_cnt], ");
            sb.AppendFormat("pd.[bene_unique_cnt] AS [bene_unique_cnt], pd.[bene_day_srvc_cnt] AS [bene_day_srvc_cnt], ");
            sb.AppendFormat("pd.[average_Medicare_allowed_amt] AS [average_Medicare_allowed_amt], ");
            sb.AppendFormat("pd.[stdev_Medicare_allowed_amt] AS [stdev_Medicare_allowed_amt], ");
            sb.AppendFormat("pd.[average_submitted_chrg_amt] AS [average_submitted_chrg_amt], ");
            sb.AppendFormat("pd.[stdev_submitted_chrg_amt] AS [stdev_submitted_chrg_amt], ");
            sb.AppendFormat("pd.[average_Medicare_payment_amt] AS [average_Medicare_payment_amt], ");
            sb.AppendFormat("pd.[stdev_Medicare_payment_amt] AS [stdev_Medicare_payment_amt], ");
            sb.AppendFormat("[pi].[nppes_provider_first_name] AS [nppes_provider_first_name], ");
            sb.AppendFormat("[pi].[nppes_provider_zip] AS [nppes_provider_zip], [pi].[provider_type] AS [provider_type],");
            sb.AppendFormat("[pi].[nppes_credentials], [pi].nppes_provider_last_org_name, [pi].nppes_provider_gender,");
            sb.AppendFormat("[pi].nppes_provider_street1, [pi].nppes_provider_city, [pi].nppes_provider_state ");
            sb.AppendFormat(" FROM  [dbo].[ProviderDetails] AS pd");
            sb.AppendFormat(" INNER JOIN [dbo].[ProviderInfo] AS [pi] ON pd.[ProviderId] = [pi].[ProviderId] ");
            sb.AppendFormat(" WHERE pd.[hcpcs_code] IN (N'99203',N'99213') ) AS [Filter1] ");
            sb.AppendFormat(" LEFT OUTER JOIN [dbo].[ProviderInfo] AS [Extent3] ON [Filter1].[ProviderId1] = [Extent3].[ProviderId] ");
            if (providerType != "")
            {
                sb.AppendFormat(" WHERE ([Filter1].[provider_type] = '{0}')  ", providerType);
            }
            else
            {
                sb.Append(" WHERE (1=1)  ");
            }

            if (!string.IsNullOrEmpty(firstName))
            {
                sb.AppendFormat(" AND ([Filter1].nppes_provider_first_name like '{0}%') ", firstName);
            }
            if (!string.IsNullOrEmpty(lastName))
            {
                sb.AppendFormat(" AND ([Filter1].nppes_provider_last_org_name like '{0}%') ", lastName);
            }

            if (listZipCodeInRange.Count == 0)
            {
                if (zipCode != Constants.Constants.YourZipCode)
                {
                    sb.AppendFormat(" AND ([Filter1].nppes_provider_zip = '{0}') ", zipCode);
                }
            }
            else
            {
                sb.Append(GetZipCodeRangeString(zipCode, listZipCodeInRange));
            }

            return sb.ToString();
        }

        private string GetZipCodeRangeString(string zipCode, List<string> listZipCodeInRange)
        {
            var sb = new StringBuilder();
            sb.AppendFormat(" AND ([Filter1].nppes_provider_zip = '{0}' OR ", zipCode);
            foreach (var z in listZipCodeInRange)
            {
                sb.AppendFormat(" [Filter1].[nppes_provider_zip] like '{0}%' OR ", z);
            }

            var result = sb.ToString().TrimEnd(new[] { 'O', 'R', ' ' });
            return result + ")";
        }
    }
}